﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace Web
{
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Data.OleDb;
    using System.Data;
    using System.Collections;
    using System.IO;

    public class ExcelHelper
    {

        /// <summary>
        /// 初始化ExcelHelper
        /// </summary>
        public ExcelHelper()
        {

        }

        /// <summary>
        /// 初始化ExcelHelper
        /// </summary>
        /// <param name="excelPath">Excel文件路径</param>
        public ExcelHelper(string excelPath)
        {
            this.ExcelPath = excelPath;
        }

        private string _ExcelPath = string.Empty;

        /// <summary>
        /// Excel文件路径
        /// </summary>
        public string ExcelPath
        {
            get
            {
                if (string.IsNullOrWhiteSpace(_ExcelPath))
                {
                    throw new ArgumentNullException(_ExcelPath);
                }

                if (!File.Exists(_ExcelPath))
                {
                    throw new FileNotFoundException("文件未找到！", _ExcelPath);
                }

                return _ExcelPath;
            }
            set
            {
                _ExcelPath = value;
            }
        }


        /// <summary>
        /// 返回 打开的 OleDbConnection
        /// </summary>
        private OleDbConnection Conn
        {
            get
            {
                OleDbConnection _conn = GetConnection(ExcelPath);
                _conn.Open();
                return _conn;
            }
        }

        /// <summary>
        /// 获取Oledb连接对象
        /// </summary>
        /// <param name="excelFile">Excel文件路径</param>
        /// <returns></returns>
        private static OleDbConnection GetConnection(string excelFile)
        {
            string ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excelFile + ";Extended Properties='Excel 8.0;IMEX=2;HDR=YES'";
            if (excelFile.ToLower().IndexOf(".xlsx") > 0)
            {
                ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelFile + ";Extended Properties='Excel 12.0;IMEX=2;HDR=YES'";
            }
            return new OleDbConnection(ConnectionString);
        }

        #region 非静态方法

        /// <summary>
        /// 获取Excel中Sheet表名
        /// </summary>
        public List<string> GetSheetNames()
        {
            List<string> lstSheetNames = new List<string>();
            DataTable data = null;
            try
            {
                //获取所有表名称
                data = Conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });
                if (data == null)
                {
                    return new List<string>();
                }
                else
                {
                    foreach (DataRow dr in data.Rows)
                    {
                        string sheetName = (dr["TABLE_NAME"] ?? string.Empty).ToString().Replace("$", "").Replace("'", "");
                        if (sheetName.Contains("_FilterDatabase") || lstSheetNames.Contains(sheetName.TrimEnd('_')))
                        {
                            continue;
                        }
                        // SheetName 为数字时，将 单引号去除
                        lstSheetNames.Add(sheetName);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (Conn != null)
                {
                    Conn.Close();
                    Conn.Dispose();
                }
            }
            return lstSheetNames;
        }


        /// <summary>
        /// 获取Sheet表数据
        /// </summary>
        public DataTable GetSheetData(string sheetName, string selectColumns = "*", string whereSheet = "")
        {
            DataTable data = new DataTable();
            string cmdText = string.Empty;
            selectColumns = string.IsNullOrWhiteSpace(selectColumns) ? "*" : selectColumns;
            try
            {
                sheetName = sheetName.IndexOf("$") > -1 ? sheetName : sheetName + "$";
                cmdText = "select " + selectColumns + " from [{0}] where 1=1 " + (string.IsNullOrEmpty(whereSheet) ? "" : whereSheet);
                cmdText = string.Format(cmdText, sheetName);
                OleDbDataAdapter adapter = new OleDbDataAdapter(cmdText, Conn);
                adapter.Fill(data);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (Conn != null)
                {
                    Conn.Close();
                    Conn.Dispose();
                }
            }
            return data;
        }





        /// <summary>
        /// 获取Excel中所有Sheet表数据
        /// </summary>
        /// <param name="excelFile">Excel文件路径</param>
        /// <returns></returns>
        public Dictionary<string, DataTable> GetAllSheetData()
        {
            Dictionary<string, DataTable> toReturn = new Dictionary<string, DataTable>();
            string tableNametoFill = string.Empty;
            DataTable dtData = null;
            string selectCommand = string.Empty;
            try
            {

                List<string> lstSheetNames = GetSheetNames();
                foreach (string sheetName in lstSheetNames)
                {
                    tableNametoFill = sheetName;
                    selectCommand = string.Format("SELECT * FROM [{0}]", tableNametoFill + "$");
                    OleDbDataAdapter oleda = new OleDbDataAdapter(selectCommand, Conn);
                    dtData = new DataTable();
                    oleda.Fill(dtData);
                    toReturn.Add(tableNametoFill, dtData);
                }
                return toReturn;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (Conn != null)
                {
                    Conn.Close();
                    Conn.Dispose();
                }
            }
        }

        /// <summary>
        /// 更加SheetName 获取 所有列名
        /// </summary>
        /// <param name="excelFile"></param>
        /// <param name="sheetName"></param>
        /// <returns></returns>
        public List<string> GetColumnNames(string sheetName)
        {
            List<string> lstColumnName = new List<string>();
            DataTable schemaTable = new DataTable();
            try
            {
                sheetName = sheetName.IndexOf("$") > -1 ? sheetName : sheetName + "$";
                schemaTable = Conn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns,
                    new Object[] { null, null, sheetName, null });
                foreach (DataRow row in schemaTable.Rows)
                {
                    lstColumnName.Add(row[3].ToString());
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (Conn != null)
                {
                    Conn.Close();
                    Conn.Dispose();
                }
            }
            return lstColumnName;
        }


        public int ExecuteNonQuery(string strSql)
        {
            var cmd = Conn.CreateCommand();
            try
            {
                cmd.CommandText = strSql;
                int res = cmd.ExecuteNonQuery();
                return res;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (Conn != null)
                {
                    Conn.Close();
                    Conn.Dispose();
                }
            }
        }


        #endregion

        #region 静态方法

        public static DataTable CreateDataTable(params string[] columnNames)
        {
            DataTable data = new DataTable();
            if (columnNames != null && columnNames.Length > 0)
            {
                data.Columns.AddRange(columnNames.Select(t => new DataColumn(t)).ToArray());
            }
            return data;
        }


        /// <summary>
        /// 导出Excel
        /// </summary>
        /// <param name="Table">DataTable数据</param>
        /// <param name="ExcelFilePath">导出Excel文件路径（存在则删除）</param>
        /// <returns></returns>
        public static bool OutputToExcel(DataTable Table, string ExcelFilePath)
        {
            if (File.Exists(ExcelFilePath))
            {
                File.Delete(ExcelFilePath);
            }

            if ((Table.TableName.Trim().Length == 0) || (Table.TableName.ToLower() == "table"))
            {
                Table.TableName = "Sheet1";
            }

            //数据表的列数
            int ColCount = Table.Columns.Count;

            //用于记数，实例化参数时的序号
            int i = 0;

            //创建参数
            OleDbParameter[] para = new OleDbParameter[ColCount];

            //创建表结构的SQL语句
            string TableStructStr = @"Create Table " + Table.TableName + "(";

            //连接字符串
            string ConnString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ExcelFilePath + ";Extended Properties=Excel 8.0;";
            OleDbConnection objConn = new OleDbConnection(ConnString);

            //创建表结构
            OleDbCommand objCmd = new OleDbCommand();

            //数据类型集合
            ArrayList DataTypeList = new ArrayList();
            DataTypeList.Add("System.Decimal");
            DataTypeList.Add("System.Double");
            DataTypeList.Add("System.Int16");
            DataTypeList.Add("System.Int32");
            DataTypeList.Add("System.Int64");
            DataTypeList.Add("System.Single");

            //遍历数据表的所有列，用于创建表结构
            foreach (DataColumn col in Table.Columns)
            {
                //如果列属于数字列，则设置该列的数据类型为double
                if (DataTypeList.IndexOf(col.DataType.ToString()) >= 0)
                {
                    para[i] = new OleDbParameter("@" + col.ColumnName, OleDbType.Double);
                    objCmd.Parameters.Add(para[i]);

                    //如果是最后一列
                    if (i + 1 == ColCount)
                    {
                        TableStructStr += col.ColumnName + " double)";
                    }
                    else
                    {
                        TableStructStr += col.ColumnName + " double,";
                    }
                }
                else
                {
                    para[i] = new OleDbParameter("@" + col.ColumnName, OleDbType.VarChar);
                    objCmd.Parameters.Add(para[i]);

                    //如果是最后一列
                    if (i + 1 == ColCount)
                    {
                        TableStructStr += col.ColumnName + " varchar)";
                    }
                    else
                    {
                        TableStructStr += col.ColumnName + " varchar,";
                    }
                }
                i++;
            }

            //创建Excel文件及文件结构
            try
            {
                objCmd.Connection = objConn;
                objCmd.CommandText = TableStructStr;

                if (objConn.State == ConnectionState.Closed)
                {
                    objConn.Open();
                }
                objCmd.ExecuteNonQuery();
            }
            catch (Exception exp)
            {
                throw exp;
            }

            //插入记录的SQL语句
            string InsertSql_1 = "Insert into " + Table.TableName + " (";
            string InsertSql_2 = " Values (";
            string InsertSql = "";

            //遍历所有列，用于插入记录，在此创建插入记录的SQL语句
            for (int colID = 0; colID < ColCount; colID++)
            {
                if (colID + 1 == ColCount)  //最后一列
                {
                    InsertSql_1 += Table.Columns[colID].ColumnName + ")";
                    InsertSql_2 += "@" + Table.Columns[colID].ColumnName + ")";
                }
                else
                {
                    InsertSql_1 += Table.Columns[colID].ColumnName + ",";
                    InsertSql_2 += "@" + Table.Columns[colID].ColumnName + ",";
                }
            }

            InsertSql = InsertSql_1 + InsertSql_2;

            //遍历数据表的所有数据行
            for (int rowID = 0; rowID < Table.Rows.Count; rowID++)
            {
                for (int colID = 0; colID < ColCount; colID++)
                {
                    if (para[colID].DbType == DbType.Double && Table.Rows[rowID][colID].ToString().Trim() == "")
                    {
                        para[colID].Value = 0;
                    }
                    else
                    {
                        para[colID].Value = Table.Rows[rowID][colID].ToString().Trim();
                    }
                }
                try
                {
                    objCmd.CommandText = InsertSql;
                    objCmd.ExecuteNonQuery();
                }
                catch (Exception exp)
                {
                    string str = exp.Message;
                }
            }
            try
            {
                if (objConn.State == ConnectionState.Open)
                {
                    objConn.Close();
                }
            }
            catch (Exception exp)
            {
                throw exp;
            }
            return true;
        }
        #endregion
    }
}